﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SSE657.BahMap.Common;

namespace SSE657.BahMap.DataAccess.SQLServer
{
    public class SchoolDataSQLServerConnector : ISchoolDataConnector
    {
        public IEnumerable<SchoolData> GetSchools(int zipcode)
        {
            using (BAHMapSqlServerEntities entities = new BAHMapSqlServerEntities())
            {
                IEnumerable<School> schools = entities.Schools.Where(s => s.ZIPCode == zipcode).AsEnumerable();

                List<SchoolData> results = new List<SchoolData>();
                foreach (School school in schools)
                {
                    results.Add(ConvertToSchoolData(school));
                }
                return results;
            }
        }

        public IEnumerable<SchoolData> GetSchools(string mha)
        {
            HashSet<int> zips = new HashSet<int>(GetZips(mha)); // most efficient for .Contains
            List<SchoolData> results = new List<SchoolData>();

            using (BAHMapSqlServerEntities entities = new BAHMapSqlServerEntities())
            {
                var query = from u in entities.Schools
                            where zips.Contains(u.ZIPCode)
                            select u;
                IEnumerable<School> schools = query.AsEnumerable();

                foreach (School school in schools)
                {
                    results.Add(ConvertToSchoolData(school));
                }
            }

            //foreach (int zip in zips)
            //{
            //    results.AddRange(GetSchools(zip));
            //}
            return results;
        }

        private IEnumerable<int> GetZips(string mha)
        {
            using (BAHMapSqlServerEntities entities = new BAHMapSqlServerEntities())
            {
                return new HashSet<int>(entities.MHA_ZIPs.Where(m => m.MHA == mha).Select(m => m.ZIPCode));
            }
        }

        private SchoolData ConvertToSchoolData(School school)
        {
            return new SchoolData()
            {
                SchoolName = school.SchoolName,
                InStateTuition = Convert.ToDouble(school.InStateTuition),
                InStateFees = Convert.ToDouble(school.InStateFees),
                OutOfStateTuition = Convert.ToDouble(school.OutOfStateTuition),
                OutOfStateFees = Convert.ToDouble(school.OutOfStateFees),
                SchoolType = (SchoolType)Enum.Parse(typeof(SchoolType), school.SchoolType)
            };
        }
    }
}
